"""
ImportRecord model - temporary staging table for batch imports.

Stores imported records before finalization to leads table.
Supports validation, normalization, deduplication, and manual review.
"""

from __future__ import annotations

from typing import TYPE_CHECKING, Any
from uuid import UUID

from sqlalchemy import JSON, Boolean, Float, ForeignKey, Integer, String, Text
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.dialects.postgresql import UUID as PostgresUUID

from ..db.base_class import Base
from ..models.base import TimestampMixin

if TYPE_CHECKING:
    from ..models.import_batch import ImportBatch


class ImportRecord(Base, TimestampMixin):
    """Temporary staging record for batch import workflow.

    Records flow: CSV upload -> ImportRecord -> manual review -> Lead (fact table)

    Attributes:
        id: Primary key
        import_batch_id: Reference to import batch
        original_data: Raw data from Excel/CSV as JSON
        normalized_data: Standardized fields (phone, name, area) as JSON
        ai_tags: AI-generated tags with confidence scores as JSON
        duplicate_candidates: List of potential duplicate records with similarity scores
        confidence: Overall confidence score (0.0 - 1.0)
        status: Current status (pending/confirmed/merged/rejected)
        review_notes: Optional notes from manual review
        finalized: Whether record has been written to leads table
        finalized_lead_id: ID of created lead after finalization
        created_at: Record creation timestamp (from TimestampMixin)
        updated_at: Last update timestamp (from TimestampMixin)
    """

    __tablename__ = "import_records"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, index=True)
    import_batch_id: Mapped[UUID] = mapped_column(
        ForeignKey("importbatch.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

    # Raw and processed data
    original_data: Mapped[dict[str, Any]] = mapped_column(
        JSON,
        nullable=False,
        comment="Original Excel/CSV row: {name, phone, area, remark, etc}",
    )
    normalized_data: Mapped[dict[str, Any]] = mapped_column(
        JSON,
        nullable=False,
        comment="Standardized fields: {name_norm, phone_norm, area_norm}",
    )

    # AI processing results
    ai_tags: Mapped[dict[str, Any] | None] = mapped_column(
        JSON,
        nullable=True,
        comment="AI-generated tags: [{name, confidence, source}]",
    )
    ai_extraction_error: Mapped[str | None] = mapped_column(
        Text,
        nullable=True,
        comment="Error message when AI tag extraction fails",
    )
    ai_extraction_attempts: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        default=0,
        comment="Number of AI extraction attempts (for monitoring)",
    )
    duplicate_candidates: Mapped[dict[str, Any] | None] = mapped_column(
        JSON,
        nullable=True,
        comment="Potential duplicates: [{record_id, score, match_type}]",
    )

    # Quality metrics
    confidence: Mapped[float] = mapped_column(
        Float,
        nullable=False,
        default=0.0,
        comment="Overall confidence score 0.0-1.0",
    )

    # Review workflow
    status: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        default="pending",
        index=True,
        comment="pending/confirmed/merged/rejected",
    )
    review_notes: Mapped[str | None] = mapped_column(
        Text,
        nullable=True,
        comment="Manual review notes from admin",
    )

    # Finalization tracking
    finalized: Mapped[bool] = mapped_column(
        Boolean,
        nullable=False,
        default=False,
        index=True,
        comment="Whether written to leads table",
    )
    finalized_lead_id: Mapped[int | None] = mapped_column(
        Integer,
        nullable=True,
        index=True,
        comment="ID of created lead after finalization",
    )

    # Relationships
    import_batch: Mapped[ImportBatch] = relationship(
        "ImportBatch",
        back_populates="import_records",
    )
